package jp.co.nes.awssupport.access.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import jp.co.nes.awssupport.common.base.CommonException;
import jp.co.nes.awssupport.common.bean.ReservedManageBean;
import jp.co.nes.awssupport.common.dao.BaseJdbcDao;
import jp.co.nes.awssupport.common.dao.BeanProcessorEx;
import jp.co.nes.awssupport.common.utility.DateUtil;
import jp.co.nes.awssupport.common.utility.DbConnUtil;

/**
 * リザーブド管理DAOです。
 */
public class ReservedManageDao extends BaseJdbcDao<ReservedManageBean> {

    /** リザーブド管理を検索する際に使用するSelect文 */
    private static final String SEL = ""
            + "select ReservedInstanceId,"
            + "       SubscriptionId,"
            + "       PricingPlanId,"
            + "       LinkedAccountId,"
            + "       ProductName,"
            + "       RegionCode,"
            + "       MultiAz,"
            + "       ProductDescription,"
            + "       InstanceType,"
            + "       UsageStartDate,"
            + "       UsageEndDate,"
            + "       OfferingType,"
            + "       OfferingClass,"
            + "       Duration,"
            + "       Quantity,"
            + "       ReservationARN,"
            + "       YearMonth"
            + "  from reserved_manage"
            + " where ReservedInstanceId = ?";

    /** リザーブド管理を検索する際のSelect文 */
    private static final String SEL_LIST = ""
            + "select ReservedInstanceId,"
            + "       SubscriptionId,"
            + "       PricingPlanId, "
            + "       LinkedAccountId,"
            + "       ProductName,"
            + "       RegionCode,"
            + "       MultiAz,"
            + "       ProductDescription,"
            + "       InstanceType,"
            + "       UsageStartDate, "
            + "       UsageEndDate,"
            + "       OfferingType,"
            + "       OfferingClass,"
            + "       Duration,"
            + "       Quantity,"
            + "       ReservationARN,"
            + "       YearMonth"
            + "  from reserved_manage"
            + " where LinkedAccountId = ?"
            + "   and ProductName = ?";

    /** リザーブド管理を検索する際に使用するSelect文 */
    private static final String SEL_MAP = ""
            + "select ReservedInstanceId,"
            + "       SubscriptionId,"
            + "       PricingPlanId,"
            + "       LinkedAccountId,"
            + "       ProductName,"
            + "       RegionCode,"
            + "       MultiAz,"
            + "       ProductDescription,"
            + "       InstanceType,"
            + "       UsageStartDate,"
            + "       UsageEndDate,"
            + "       OfferingType,"
            + "       OfferingClass,"
            + "       Duration,"
            + "       Quantity,"
            + "       ReservationARN,"
            + "       YearMonth"
            + "  from reserved_manage"
            + " where DATE_FORMAT(UsageStartDate,'%Y%m') <= ?"
            + "   and DATE_FORMAT(UsageEndDate,'%Y%m') >= ?";

    /** リザーブド管理を登録する際に使用するInsert文 */
    private static final String BATCH_INS = ""
            + "insert into reserved_manage ("
            + "    ReservedInstanceId,"
            + "    SubscriptionId,"
            + "    PricingPlanId,"
            + "    LinkedAccountId,"
            + "    ProductName,"
            + "    RegionCode,"
            + "    MultiAz,"
            + "    ProductDescription,"
            + "    InstanceType,"
            + "    UsageStartDate,"
            + "    UsageEndDate,"
            + "    OfferingType,"
            + "    OfferingClass,"
            + "    Duration,"
            + "    Quantity,"
            + "    ReservationARN,"
            + "    YearMonth)"
            + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    /** リザーブド管理を削除する際に使用するDeletet文 */
    private static final String DEL_YEARMONTH = ""
            + "delete from reserved_manage"
            + " where YearMonth = ?";

    /**
     * デフォルトコンストラクタです。
     */
    public ReservedManageDao() {
    }

    /**
     * 引数に該当するリザーブド管理を返却します。<br>
     * 該当データが存在しない場合は、nullを返却します。
     *
     * @param reservedInstanceId リザーブドインスタンスID
     * @return リザーブド管理
     */
    public ReservedManageBean get(String reservedInstanceId) {
        try {
            Connection connection = DbConnUtil.getConn();

            List<Object> paramList = new ArrayList<>();
            paramList.add(reservedInstanceId);
            Object[] params = paramList.toArray();

            QueryRunner runner = new QueryRunner();
            return runner.query(connection, SEL,
                    new BeanHandler<>(ReservedManageBean.class, new BasicRowProcessor(new BeanProcessorEx())),
                    params);
        } catch (SQLException e) {
            throw new CommonException(e);
        }
    }

    /**
     * 引数に該当するリザーブド管理リストを返却します。<br>
     * 該当データが存在しない場合は、空のリストを返却します。
     *
     * @param linkedAccountId 連結アカウントID
     * @param productName 製品名
     * @return リザーブド管理リスト
     */
    public List<ReservedManageBean> getList(String linkedAccountId, String productName) {
        try {
            Connection connection = DbConnUtil.getConn();

            Object[] params = new Object[2];
            params[0] = linkedAccountId;
            params[1] = productName;

            QueryRunner runner = new QueryRunner();
            return runner.query(connection, SEL_LIST,
                    new BeanListHandler<>(ReservedManageBean.class, new BasicRowProcessor(new BeanProcessorEx())),
                    params);
        } catch (SQLException e) {
            throw new CommonException(e);
        }
    }

    /**
     * 引数に該当するリザーブド管理マップを返却します。<br>
     * 該当データが存在しない場合は、空のマップを返却します。
     *
     * @param targetYearMonth 処理対象年月
     * @return リザーブド管理マップ(キー=リザーブドインスタンスID、値=リザーブド管理)
     */
    public Map<String, ReservedManageBean> getMap(YearMonth targetYearMonth) {
        try {
            Connection connection = DbConnUtil.getConn();

            List<Object> paramList = new ArrayList<>();
            paramList.add(DateUtil.toString(targetYearMonth));
            paramList.add(DateUtil.toString(targetYearMonth));
            Object[] params = paramList.toArray();

            QueryRunner runner = new QueryRunner();
            List<ReservedManageBean> beanList = runner.query(connection, SEL_MAP,
                    new BeanListHandler<>(ReservedManageBean.class, new BasicRowProcessor(new BeanProcessorEx())),
                    params);
            return beanList.stream().collect(Collectors.toMap(bean -> {
                return bean.getReservedInstanceId();
            }, bean -> bean));
        } catch (SQLException e) {
            throw new CommonException(e);
        }
    }

    /**
     * 引数に該当するリザーブド管理を削除します。
     *
     * @param yearMonth 処理対象年月
     */
    public void delete(YearMonth yearMonth) {
        try {
            Connection connection = DbConnUtil.getConn();

            Object[] params = new Object[1];
            params[0] = DateUtil.toInt(yearMonth);

            QueryRunner runner = new QueryRunner();
            runner.update(connection, DEL_YEARMONTH, params);
        } catch (SQLException e) {
            throw new CommonException(e);
        }
    }

    /*
     * (非 Javadoc)
     * @see jp.co.nes.awssupport.common.dao.BaseJdbcDao#getBatchInsQuery()
     */
    @Override
    protected String getBatchInsQuery() {
        return BATCH_INS;
    }

    /*
     * (非 Javadoc)
     * @see jp.co.nes.awssupport.common.dao.BaseJdbcDao#toBatchInsObjectList(jp.co.nes.awssupport.common.bean.BaseBean)
     */
    @Override
    protected List<Object> toBatchInsObjectList(ReservedManageBean bean) {
        List<Object> objectList = new ArrayList<>();
        objectList.add(bean.getReservedInstanceId());
        objectList.add(bean.getSubscriptionId());
        objectList.add(bean.getPricingPlanId());
        objectList.add(bean.getLinkedAccountId());
        objectList.add(bean.getProductName());
        objectList.add(bean.getRegionCode());
        objectList.add(bean.getMultiAZ());
        objectList.add(bean.getProductDescription());
        objectList.add(bean.getInstanceType());
        objectList.add(DateUtil.toTimestamp(bean.getUsageStartDate()));
        objectList.add(DateUtil.toTimestamp(bean.getUsageEndDate()));
        objectList.add(bean.getOfferingType());
        objectList.add(bean.getOfferingClass());
        objectList.add(bean.getDuration());
        objectList.add(bean.getQuantity());
        objectList.add(bean.getReservationARN());
        objectList.add(bean.getYearMonth());
        return objectList;
    }
}
